﻿-- ============================================= 
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_Create] 
	(
		@StartDate NCHAR(10),
		@EndDate NCHAR(10),
		@userid VARCHAR(10),
		@username NVARCHAR(10)
	)
AS
BEGIN
	
---------------------------------------------------------------------------------------------
DECLARE @bd int,@wz int,@qt INT,@qyQQ INT,@qytz INT,@gdzc int,@xfml DECIMAL(18,2),@xsml DECIMAL(18,2),@xse DECIMAL(18,2)
DECLARE @mlhj DECIMAL(18,2),@xzys DECIMAL(18,2),@wksr DECIMAL(18,2),@tx DECIMAL(18,2),@rcfy DECIMAL(18,2),@cl DECIMAL(18,2)
DECLARE @sscl DECIMAL(18,2),@zxf DECIMAL(18,2)
---------------------------------------------------------------------------------------------

Declare @indextable Table(id int Identity(1,1),cid int,cname nvarchar(100),nid int,maname nvarchar(50))
Insert Into @indextable(cid,cname,nid,maname) Select Id,CompanyName,Mid,(SELECT ma.CompanyName
                                                                         FROM
                                                                         	ManagementArea ma WHERE ma.Id=Company.Mid)
                                                From Company WHERE id <>6

Declare @start int, @end int, @companyId int ,@companyName nvarchar(50), @today INT,@nid INT,@maname  nvarchar(50)
	Select  @end=Count(Id) From @indextable
	Select  @start=1
	While(@start<=@end)
BEGIN
	Select @companyId=cid,@companyName=cname,@nid=nid,@maname=maname
	 From @indextable Where Id=@start


--ProductId in (1,37) 百度数AND NOE_Flag<>2
SET @bd= ISNULL((SELECT  COUNT(DISTINCT orderid)
  FROM Project AS p WHERE CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate AND NOE_Flag<>2
AND companyid=@companyId AND ProductId in (1,37) AND typeid=1  and p.OrderId NOT IN (select oid from (SELECT oid FROM 
Order_Change oc WHERE oid IN (SELECT distinct orderid FROM Project p2 WHERE 
                       p2.CompanyId=@companyId AND  p2.CreateDate<@StartDate and p2.OrderId=p.OrderId )

 AND CONVERT(VARCHAR(10),oc.shqrq,120) BETWEEN  @StartDate AND @EndDate 
 UNION 

SELECT oid FROM [Order] op WHERE op.StateId=6 AND op.CompanyId=@companyId 
     AND  CONVERT(VARCHAR(10),op.AuditDate,120) BETWEEN @StartDate AND @EndDate ) AS nb1

)  AND p.SalePrice!=0  
 
  --AND p.OrderId NOT IN (SELECT oid FROM [Order] o WHERE o.StateId=6 AND o.CompanyId=@companyId 
  --   AND  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate )
 AND Id NOT IN (SELECT  Project_Id 
                  FROM Order_Change_Project where Order_Change_Project.companyid =@companyId AND Order_Change_Project.Project_Id!=0)
 AND CHARINDEX('对冲',Convert(varchar(8000),p.[Description]))<=0),0)  

--ProductId in (50,51)网站数AND NOE_Flag<>2 
SET @wz=ISNULL(( SELECT 
 COUNT(DISTINCT orderid)
  FROM Project AS p WHERE CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate AND NOE_Flag<>2
AND companyid =@companyId AND ProductId in (50,51) AND typeid=1  and p.OrderId NOT IN (select oid from (SELECT oid FROM 
Order_Change oc WHERE  oid IN (SELECT distinct orderid FROM Project p2 WHERE p2.CompanyId=@companyId AND  p2.CreateDate<@StartDate  and p2.OrderId=p.OrderId )

 AND CONVERT(VARCHAR(10),oc.shqrq,120) BETWEEN  @StartDate AND @EndDate
UNION 
SELECT oid FROM [Order] op WHERE op.StateId=6 AND op.CompanyId=@companyId 
     AND  CONVERT(VARCHAR(10),op.AuditDate,120) BETWEEN @StartDate AND @EndDate ) AS nb2
)   AND p.SalePrice!=0
  --AND p.OrderId NOT IN (SELECT oid FROM [Order] o WHERE o.StateId=6 AND o.CompanyId=@companyId 
  --   AND  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate )
 AND Id NOT IN (SELECT  Project_Id 
                  FROM Order_Change_Project where Order_Change_Project.companyid =@companyId  AND Order_Change_Project.Project_Id!=0)
 AND CHARINDEX('对冲',Convert(varchar(8000),p.[Description]))<=0
),0)  

--ProductId in (68,71,112,116)企业QQ数AND NOE_Flag<>2
SET @qyQQ=ISNULL(( SELECT 
 COUNT(DISTINCT orderid)
  FROM Project AS p WHERE CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate AND NOE_Flag<>2
AND companyid =@companyId AND ProductId in (68,71,112,116) AND typeid=1  and p.OrderId NOT IN (select oid from (SELECT oid FROM 
Order_Change oc WHERE  oid IN (SELECT distinct orderid FROM Project p2 WHERE p2.CompanyId=@companyId AND  p2.CreateDate<@StartDate  and p2.OrderId=p.OrderId )

 AND CONVERT(VARCHAR(10),oc.shqrq,120) BETWEEN  @StartDate AND @EndDate

UNION 
SELECT oid FROM [Order] op WHERE op.StateId=6 AND op.CompanyId=@companyId 
     AND  CONVERT(VARCHAR(10),op.AuditDate,120) BETWEEN @StartDate AND @EndDate) AS nb3
)   AND p.SalePrice!=0
  --AND p.OrderId NOT IN (SELECT oid FROM [Order] o WHERE o.StateId=6 AND o.CompanyId=@companyId 
  --   AND  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate )
 AND Id NOT IN (SELECT  Project_Id 
                  FROM Order_Change_Project where Order_Change_Project.companyid =@companyId  AND Order_Change_Project.Project_Id!=0)
 AND CHARINDEX('对冲',Convert(varchar(8000),p.[Description]))<=0
),0)  

--ProductId in (39)通址数AND NOE_Flag<>2
SET @qytz=ISNULL(( SELECT 
 COUNT(DISTINCT orderid)
  FROM Project AS p WHERE CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate AND NOE_Flag<>2
AND companyid =@companyId AND ProductId in (39) AND typeid=1  and p.OrderId NOT IN (select oid from (SELECT oid FROM 
Order_Change oc WHERE  oid IN (SELECT distinct orderid FROM Project p2 WHERE p2.CompanyId=@companyId AND  p2.CreateDate<@StartDate  and p2.OrderId=p.OrderId )

 AND CONVERT(VARCHAR(10),oc.shqrq,120) BETWEEN  @StartDate AND @EndDate

UNION 
SELECT oid FROM [Order] op WHERE op.StateId=6 AND op.CompanyId=@companyId 
     AND  CONVERT(VARCHAR(10),op.AuditDate,120) BETWEEN @StartDate AND @EndDate) AS nb4

)   AND p.SalePrice!=0

  --AND p.OrderId NOT IN (SELECT oid FROM [Order] o WHERE o.StateId=6 AND o.CompanyId=@companyId 
  --   AND  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate )
 AND Id NOT IN (SELECT  Project_Id 
                  FROM Order_Change_Project where Order_Change_Project.companyid =@companyId  AND Order_Change_Project.Project_Id!=0)
 AND CHARINDEX('对冲',Convert(varchar(8000),p.[Description]))<=0
),0)  

--ProductId not in (1,37,50,51,68,71,112,116,39) 其他数AND NOE_Flag<>2
SET @qt= ISNULL((SELECT 
 COUNT(DISTINCT orderid)
  FROM Project AS p WHERE CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate AND NOE_Flag<>2
AND companyid =@companyId AND ProductId not in (1,37,50,51,68,71,112,116,39,22,29,33) AND typeid=1 
 and p.OrderId NOT IN (select oid from (SELECT oid FROM 
Order_Change oc WHERE  oid IN (SELECT distinct orderid FROM Project p2 WHERE p2.CompanyId=@companyId AND  p2.CreateDate<@StartDate and p2.OrderId=p.OrderId  )

 AND CONVERT(VARCHAR(10),oc.shqrq,120) BETWEEN  @StartDate AND @EndDate
UNION 
SELECT oid FROM [Order] op WHERE op.StateId=6 AND op.CompanyId=@companyId 
     AND  CONVERT(VARCHAR(10),op.AuditDate,120) BETWEEN @StartDate AND @EndDate) AS nb5
)   AND p.SalePrice!=0

  --AND p.OrderId NOT IN (SELECT oid FROM [Order] o WHERE o.StateId=6 AND o.CompanyId=@companyId 
  --   AND  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate )
 AND Id NOT IN (SELECT  Project_Id 
                  FROM Order_Change_Project where Order_Change_Project.companyid =@companyId  AND Order_Change_Project.Project_Id!=0)
 AND CHARINDEX('对冲',Convert(varchar(8000),p.[Description]))<=0
 ),0)  

--续费毛利AND NOE_Flag<>2 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @xfml=ISNULL(( SELECT sum(saleprice-costprice) AS 毛利 FROM project WHERE 
CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate  
AND companyid =@companyId  AND typeid=2   AND ProductId not IN (22,29,33) AND NOE_Flag<>2 ),0)+
ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge WHERE CompanyId 
=@companyId and Convert(nchar(10),UpTime,120) BETWEEN @StartDate AND @EndDate
 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)

--销售毛利 AND NOE_Flag<>2 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @xsml=ISNULL(( SELECT sum(saleprice-costprice) AS 毛利 FROM project WHERE 
CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate  
AND companyid =@companyId AND typeid=1   AND ProductId not IN (22,29,33) AND NOE_Flag<>2),0)+
ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge WHERE CompanyId =@companyId
 and Convert(nchar(10),UpTime,120) BETWEEN @StartDate AND @EndDate
  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)

------------------------------------------------------------------------------------------------------------
--销售额 AND NOE_Flag<>2 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @xse=ISNULL(( SELECT SUM (YingShouPrice) FROM [Order] WHERE 
  CONVERT(VARCHAR(10),AuditDate,120) BETWEEN @StartDate AND @EndDate  AND StateId>=1 
AND companyid =@companyId   AND NOE_Flag<>2),0)
+
ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge WHERE CompanyId =@companyId
 and Convert(nchar(10),UpTime,120)  BETWEEN @StartDate AND @EndDate
 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 ) )
 ,0)

--毛利合计 AND NOE_Flag<>2and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @mlhj=ISNULL(( SELECT sum(saleprice-costprice) AS 毛利 FROM project WHERE 
CONVERT(VARCHAR(10),createdate,120) BETWEEN @StartDate AND @EndDate  
AND companyid =@companyId   AND ProductId not IN (22,29,33)  AND NOE_Flag<>2),0)+
ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge WHERE CompanyId 
=@companyId and Convert(nchar(10),UpTime,120) BETWEEN @StartDate AND @EndDate
and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)+
ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge WHERE CompanyId =@companyId
 and Convert(nchar(10),UpTime,120) BETWEEN @StartDate AND @EndDate
 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)


--新增应收  AND NOE_Flag<>2 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @xzys=ISNULL(( Select Sum(YingShouPrice-ShiShouPrice) From [Order] WHERE CompanyId =@companyId
and Convert(nchar(10),AuditDate,120) BETWEEN @StartDate AND @EndDate  AND StateId>=1   AND NOE_Flag<>2 ),0)+
ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge WHERE CompanyId =@companyId
 and Convert(nchar(10),UpTime,120)  BETWEEN @StartDate AND @EndDate 
  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)

--尾款收入and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
SET @wksr=ISNULL(( Select Sum(Prices) From Order_PaymentRecord Where CompanyId =@companyId 
and Convert(nchar(10),FinanceDate,120) BETWEEN @StartDate AND @EndDate
and SWId=2  and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )),0)+
ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge WHERE CompanyId =@companyId and 
Convert(nchar(10),UpTime,120)  BETWEEN @StartDate AND @EndDate 
 and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
),0)

--摊销
SET @tx=ISNULL((SELECT SUM(cm.ConsumableMoney) FROM ConsumableMonth cm WHERE cm.CompanyIdCur=@companyId and CONVERT(NCHAR(10),cm.ConsumableDate,120) BETWEEN  @StartDate AND @EndDate),0)

--日常费用 费用类和资产类参与计算 ，二级分类里部门基金不参与 SubjectId NOT IN (181,159,191,192,204,196,201,176,177,207,208)
SET @rcfy=ISNULL(( Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdCur =@companyId 
and Convert(nchar(10),CreateDate,120) BETWEEN @StartDate AND @EndDate 
AND CateOneId IN (16,22) AND Finance_Outlay.CateTwoId NOT IN (11) 
  AND SubjectId NOT IN (181,159,191,192,204,196,201,176,177,207,208) AND Finance_Outlay.A_tx!=1),0)

--纯利
SET @cl=(@mlhj-@tx-@rcfy)

--实收纯利 
SET @sscl=(@cl+@wksr-@xzys)

--装修费(159)
SET @zxf=ISNULL(( Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdCur =@companyId 
and Convert(nchar(10),CreateDate,120) BETWEEN @StartDate AND @EndDate 
and [SubjectId]=159),0)
	
--固定资产支出(176)
SET @gdzc=ISNULL(( Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdCur =@companyId 
and Convert(nchar(10),CreateDate,120) BETWEEN @StartDate AND @EndDate 
and [SubjectId] =176),0)	
	
------------------------------------------------------------------------------------------------	

SELECT @today=COUNT(1) FROM FinanceMonthTable WHERE left(Convert(nchar(10),TMonth,120),7)=left(Convert(nchar(10),@StartDate,120),7) AND companyid=@companyId
IF 	@today>0
BEGIN
	DELETE [FinanceMonthTable] WHERE left(Convert(nchar(10),TMonth,120),7)=left(Convert(nchar(10),@StartDate,120),7) AND companyid=@companyId
END
INSERT INTO [FinanceMonthTable]
           ([MAid]
           ,[MAname]
           ,[bd]
           ,[wz]
           ,[qt]
           ,[xfml]
           ,[xsml]
           ,[xse]
           ,[mlhj]
           ,[xzys]
           ,[wksr]
           ,[tx]
           ,[rcfy]
           ,[cl]
           ,[sscl]
           ,[zxf]
           ,[createdate]
           ,[userid]
           ,[username]
           ,TMonth
           ,qyQQ
           ,tz
           ,CompanyId
           ,CompanyName
           ,gdzc)
     VALUES(
     	   @nid,
     	   @maname
           ,@bd
           ,@wz
           ,@qt
           ,@xfml
           ,@xsml
           ,@xse
           ,@mlhj
           ,@xzys
           ,@wksr
           ,@tx
           ,@rcfy
           ,@cl
           ,@sscl
           ,@zxf
           ,GETDATE()
           ,@userid
           ,@username
           ,@StartDate
           ,@qyQQ
           ,@qytz
           ,@CompanyId
           ,@companyName
           ,@gdzc
           
	)
		
	Set @start=@start+1--自增
	END
END
RETURN
